﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using IsikFx.Project.CommonTypes.CRUD;
using log4net;
using IsikFx.Project.Utils;
namespace IsikFx.Project.WcfService.Helpers
{
    public class DbHelper
    {
        OleDbConnection conn;
        OleDbCommand cmd;
        OleDbDataAdapter adap;
        DataTable dt;
        OleDbDataReader dataReader;
        public static ILog logger = LogManager.GetLogger(typeof(DbHelper));
        private bool ConnectToDB()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString;

            if (string.IsNullOrEmpty(connectionString))
                throw new ConfigurationErrorsException("ConnectionString not set");

            conn = new OleDbConnection(connectionString);

            try
            {
                conn.Open();
                return true;
            }
            catch (Exception ex)
            {
                ExceptionHelper.HandleException(ex, typeof(DbHelper));
                if (conn != null)
                    conn.Dispose();
                throw;
            }
        }

        public CrudResponseMessage ExecuteNoneQuery(string commandText, params object[] parameters)
        {
            CrudResponseMessage crudResponseMessage = new CrudResponseMessage();
            crudResponseMessage.Response = new CrudResponse();
            crudResponseMessage.HasError = false;
            if (string.IsNullOrEmpty(commandText))
                throw new Exception("CommandText is null");
            try
            {
                if (ConnectToDB())
                {
                    using (conn)
                    {
                        cmd = new OleDbCommand(commandText, conn);
                        crudResponseMessage.Response.AffectedRows = cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                crudResponseMessage.HasError = true;
                crudResponseMessage.ErrorMessage = ex.Message;
                ExceptionHelper.HandleException(ex, typeof(DbHelper));
            }
            return crudResponseMessage;
        }

        public DataTable GetDataTable(string commandText)
        {
            try
            {
                if (ConnectToDB())
                {
                    using (conn)
                    {
                        adap = new OleDbDataAdapter(commandText, conn);
                        dt = new DataTable("Table");
                        adap.Fill(dt);
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                ExceptionHelper.HandleException(ex, typeof(DbHelper));
            }
            return null;
        }

        public OleDbDataReader GetDataReader(string command)
        {
            try
            {
                if (ConnectToDB())
                {
                    cmd = new OleDbCommand(command, conn);
                    dataReader = cmd.ExecuteReader();
                }
            }
            catch (Exception ex)
            {
                ExceptionHelper.HandleException(ex, typeof(DbHelper));
            }
            return dataReader;
        }
    }
}
